# 作业：
# 1、通过python连接pymysql数据库，封装类方法
# 1.1、随机插入100条数据到数据库表，包括姓名、年龄、家庭住址、身份证号码
# 1.2、查询全部的数据
# 1.3、删除指定一条数据
# 1.4、封装批量删除方法，可以删除多条
# 1.5、可以指定增加单独一条数据到表里
# 以上每个操作步骤要写日志信息到log.txt里
import pymysql,random
from faker import Faker
from datetime import datetime
class Person:
    def __init__(self,user,password,host,database,cursorclass):
        self.user = user
        self.password = password
        self.host = host
        self.database = database
        self.cursorclass = cursorclass
        self.faker = Faker(locale='zh_cn')

    def connect(self):
        self.db = pymysql.connect(password = self.password,user = self.user,host = self.host,database = self.database,cursorclass=self.cursorclass)
        self.cursor = self.db.cursor()
    def close(self):
        self.cursor.close()
        self.db.close()
    def insert_random_num(self,count):
        # 随机插入100条数据到数据库表，包括姓名、年龄、家庭住址、身份证号码
        for i in range(count):
            name = self.faker.name() # 姓名
            age = random.randint(18,35) # 姓名
            address = self.faker.address() # 住址
            ssn = self.faker.ssn(min_age = 18,max_age=35) # 身份证号码
            sql = f"insert into person(name,age,address,ssn) values('{name}',{age},'{address}','{ssn}')"
            try:
                self.connect()
                self.cursor.execute(sql)
                self.db.commit()
                self.close()
                print(f"新增数据成功，姓名为：{name}，年龄：{age}，地址：{address}，身份证号：{ssn}")
            except Exception as ec:
                with open("log1.txt", 'a', encoding='utf-8') as f:
                    f.write(str(datetime.now()) + f" 新增数据失败，请检查数据库！")
                self.db.rollback()
                print("数据库出现错误%s"%ec)
        else:
            with open("log1.txt", 'a', encoding='utf-8') as f:
                f.write(str(datetime.now()) + f" 新增{count}条数据成功")
            print(f"新增{count}条数据成功！")

    def search_all(self):
        #查询全部的数据
        try:
            self.connect()
            self.cursor.execute('select * from person')
            result = self.cursor.fetchall()
            self.close()
            for i in range(len(result)):
                print(f"第{i+1}条数据为：{result[i]}")
                with open("log1.txt", 'a', encoding='utf-8') as f:
                    f.write(str(datetime.now()) + f"{result[i]}")
            return result
        except:
            self.db.rollback()
            return 0
    def delete(self,name):
        sql = f"delete from person where name='{name}'"
        try:
            self.connect()
            self.cursor.execute(sql)
            self.db.commit()
            print(f"删除：{name} 成功")
            self.close()
        except Exception as ec:
            self.db.rollback()
            print("数据库出现错误%s" % ec)
    def delete_num(self,*name):
        for i in range(len(name)):
            try:
                self.connect()
                self.cursor.execute(f'delete from person where name = "{name[i]}"')
                self.db.commit()
                print(f"删除：{name[i]} 成功")
                self.close()
            except Exception as ec:
                self.db.rollback()
                print("数据库出现错误%s" % ec)
    def insert_(self,name,age,address,ssn):
        sql = f"insert into person(name,age,address,ssn) values('{name}',{age},'{address}','{ssn}')"
        try:
            self.connect()
            self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except Exception as ec:
            self.db.rollback()
            print("数据库出现错误%s" % ec)


p1 = Person(host= "8.139.7.103",user= 'root',password= '123456',database='asae598',cursorclass=pymysql.cursors.DictCursor)
# p1.connect()
# p1.insert_random_num(50)
# p1.search_all()
# p1.delete("曹军")
# p1.search_all()
# p1.delete_num("钟瑜","张金凤","李洁")
# p1.search_all()
# p1.insert_('关博文',28,"辽宁省沈阳市沈阳街x座","360601199902226495")
# p1.search_all()



